﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using QuanLySinhVien_Report.DataPlayer;
using QuanLySinhVien_Report.DataObjectPlayer;

namespace QuanLySinhVien_Report.BusinessPlayer
{
    class BusinessPlayer_Sinhvien
    {
        // khai bao lop ket noi;
        DatasetPlayer data_sv = new DatasetPlayer();
        public DataTable GetDataTable_Sinhvien()
        {
            string sql = "select masv,hoten,ngaysinh,noisinh,phai,diachi,makhoa,hocbong,he,matinh from sinhvien";
            return data_sv.GetDatatable(sql);
        }
        public DataTable Laygioitinh()
        {
            string sql = "select DISTINCT phai ,"
                + " tengt =CASE phai WHEN 0 then  N'Nam' "
                + " WHEN 1 then N'Nu' END from sinhvien";
            return data_sv.GetDatatable(sql);
        }
        public void Them_Sinhvien(DataObject_SinhVien sv)
        {

            string sql = string.Format("insert into sinhvien(masv,hoten,ngaysinh,noisinh,phai,diachi,makhoa,hocbong,he,matinh)"
                + " values('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}')", sv.masv, sv.hoten, sv.ngaysinh, sv.noisinh,
                sv.phai, sv.diachi, sv.makhoa, sv.hocbong, sv.he, sv.matinh);
            if (data_sv.ChayQuery(sql))
                MessageBox.Show("Them sinh vien Thanh cong", "Thong Tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        public void Sua_Sinhvien(DataObject_SinhVien sv)
        {
            string sql = "update sinhvien set hoten='" + sv.hoten + "',ngaysinh='" + sv.ngaysinh + "',noisinh='" + sv.noisinh + "',"
             + "phai='" + sv.phai + "',diachi='" + sv.diachi + "',makhoa='" + sv.makhoa + "',hocbong='" + sv.hocbong + "',"
           + "he='" + sv.he + "',matinh='" + sv.matinh + "' where masv='" + sv.masv + "'";
            //string sql = string.Format("update sinhvien set hoten='{0}',ngaysinh='{1}',noisinh='{2}',phai={3},"
            //+"diachi='{4}',makhoa='{5}',hocbong='{6}',he='{7}',matinh='{}8' where masv='{9}'", 
            //   sv.hoten, sv.ngaysinh, sv.noisinh,sv.phai, sv.diachi, sv.makhoa, sv.hocbong, sv.he, sv.matinh, sv.masv);
            if (data_sv.ChayQuery(sql))
                MessageBox.Show("Sua sinh vien Thanh cong", "Thong Tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        public void Xoa_Sinhvien(DataObject_SinhVien sv)
        {
            string sql = "delete sinhvien where masv='" + sv.masv + "'";
            if (data_sv.ChayQuery(sql))
                MessageBox.Show("Xoa sinh vien Thanh cong", "Thong Tin", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        // kiem tra du lieu co de trong khong
        public bool kiemtrama(string Masv)
        {
            if (data_sv.kiemtra("sinhvien", "masv", Masv))
                return true;
            return false;
        }
        public bool kiemtraluu(DataObject_SinhVien sv)
        {
            if (kiemtrama(sv.masv))
            {
                MessageBox.Show("Ma so sinh vien da co trong csdl");
                return false;
            }
            if (sv.masv.Equals(""))
            {
                MessageBox.Show("Ma so sinh vien khong de trong");
                return false;
            }
            if (sv.hoten.Equals(""))
            {
                MessageBox.Show("Ten sinh vien khong de trong");
                return false;
            }

            if (sv.noisinh.Equals(""))
            {
                MessageBox.Show("Noi sinh cua sinh vien khong de trong");
                return false;
            }
            if (sv.diachi.Equals(""))
            {
                MessageBox.Show("Dia chi cua sinh vien khong de trong");
                return false;
            }
            return true;
        }
    }
}
